package AccountWindow.SystemAdmin.AccountDetailWindow.Dao;

import AccountWindow.SystemAdmin.Account;
import AccountWindow.SystemAdmin.AccountDetailWindow.AccountDetailWindow;
import SignAndRegister.RemindWindow;

import javax.swing.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;


public class AccountDetailDao {

    String url = "jdbc:mysql://localhost:3306/hims";
    String user = "root";
    String password = "511300";



    public int add(Account account) throws Exception{
        Class.forName("com.mysql.cj.jdbc.Driver"); //注册驱动
        Connection con = DriverManager.getConnection(url, user, password);  //获取连接
        Statement stat = con.createStatement(); //获取执行者对象
        int row = -1;
        String sql = "INSERT INTO account VALUES ('"+account.accountID+"','"+account.accountCode+"','"+account.accountType+"')";
        row += stat.executeUpdate(sql);
        if("住客".equals(account.accountType)){
            sql = "INSERT INTO customer (customerID,customerName,customerPhone) VALUES ('"+account.accountID+"','"+account.accountName+"','"+account.accountPhone+"')";
        } else if ("职工".equals(account.accountType)) {
            sql = "INSERT INTO employee (employeeID,employeeName,employeePhone) VALUES ('"+account.accountID+"','"+account.accountName+"','"+account.accountPhone+"')";
        } else if ("酒店管理员".equals(account.accountType)) {
            sql = "INSERT INTO hoteladmin (hotelAdminID,hotelAdminName,hotelAdminPhone) VALUES ('"+account.accountID+"','"+account.accountName+"','"+account.accountPhone+"')";
        } else if ("系统管理员".equals(account.accountType)) {
            sql = "INSERT INTO systemAdmin (systemAdminID,systemAdminName,systemAdminPhone) VALUES ('"+account.accountID+"','"+account.accountName+"','"+account.accountPhone+"')";
        }

        row += stat.executeUpdate(sql);

        stat.close();
        con.close();
        return row;
    }

    public int remove(Account account) throws Exception{
        Class.forName("com.mysql.cj.jdbc.Driver"); //注册驱动
        Connection con = DriverManager.getConnection(url, user, password);  //获取连接
        Statement stat = con.createStatement(); //获取执行者对象
        int row = -1;
        String sql;

        if("住客".equals(account.accountType)){
            sql = "DELETE FROM customer WHERE customerID = '"+account.accountID+"'";
            row += stat.executeUpdate(sql);
        }else if("职工".equals(account.accountType)){
            sql = "DELETE FROM employee WHERE employeeID = '"+account.accountID+"'";
            row += stat.executeUpdate(sql);
        }else if("酒店管理员".equals(account.accountType)){
            sql = "DELETE FROM hoteladmin WHERE hotelAdminID = '"+account.accountID+"'";
            row += stat.executeUpdate(sql);
        }else if("系统管理员".equals(account.accountType)){
            sql = "DELETE FROM systemadmin WHERE systemAdminID = '"+account.accountID+"'";
            row += stat.executeUpdate(sql);
        }

        sql = "DELETE FROM account WHERE accountID = '"+account.accountID+"'";
        row += stat.executeUpdate(sql);
        stat.close();
        con.close();

        return row;
    }

    public int modify(Account account) throws Exception{
        Class.forName("com.mysql.cj.jdbc.Driver"); //注册驱动
        Connection con = DriverManager.getConnection(url, user, password);  //获取连接
        Statement stat = con.createStatement(); //获取执行者对象
        int row = -1;

        String targetAccountID = null;
        String targetAccountType = null;
        String sql = "SELECT * FROM account WHERE accountID = '"+account.accountID+"'";
        ResultSet rs = stat.executeQuery(sql);
        while(rs.next()){
            targetAccountID = rs.getString("accountID");
            targetAccountType = rs.getString("accountType");
        }
        if(targetAccountID==null){
            new RemindWindow("NoThisAccount");
            row += 2;
        }else if(targetAccountType.equals(account.accountType)){

            sql = "UPDATE  account SET accountCode='"+account.accountCode+"'WHERE accountID ='"+account.accountID+"'";
            row += stat.executeUpdate(sql);

            if("住客".equals(account.accountType)){
                sql = "UPDATE customer SET customerName='"+account.accountName+"',customerPhone='"+account.accountPhone+"' WHERE customerID ='"+account.accountID+"'";
            } else if ("职工".equals(account.accountType)) {
                sql = "UPDATE employee SET employeeName='"+account.accountName+"',employeePhone='"+account.accountPhone+"' WHERE employeeID ='"+account.accountID+"'";
            } else if ("酒店管理员".equals(account.accountType)) {
                sql = "UPDATE hoteladmin SET hoteladminName='"+account.accountName+"',hoteladminPhone='"+account.accountPhone+"' WHERE hoteladminID ='"+account.accountID+"'";
            } else if ("系统管理员".equals(account.accountType)) {
                sql = "UPDATE systemadmin SET systemadminName='"+account.accountName+"',systemadminPhone='"+account.accountPhone+"' WHERE systemadminID ='"+account.accountID+"'";
            }

            row += stat.executeUpdate(sql);

        }else {
            new RemindWindow("DontModifyType");
            row += 2;
        }

        stat.close();
        con.close();
        return row;
    }



    public Account queryByID(String ID) throws Exception{

        Account queryByIDAccount = new Account(null);

        if(ID==null){
            return null;
        }else{
            Class.forName("com.mysql.cj.jdbc.Driver"); //注册驱动
            Connection con = DriverManager.getConnection(url, user, password);  //获取连接
            Statement stat = con.createStatement(); //获取执行者对象
            String sql = "SELECT * FROM account WHERE accountID = '"+ID+"'";
            ResultSet rs =stat.executeQuery(sql);

            while(rs.next()) {

                String accountID = rs.getString("accountID");
                String accountCode = rs.getString("accountCode");
                String accountType = rs.getString("accountType");
                String accountPhone = null;
                String accountName = null;

                if ("住客".equals(accountType)) {
                    sql = "SELECT * FROM customer WHERE customerID = '" + accountID + "'";
                    Connection conTmp = DriverManager.getConnection(url, user, password);
                    Statement statTmp = conTmp.createStatement();
                    ResultSet rsTmp = statTmp.executeQuery(sql);
                    while (rsTmp.next()) {
                        accountPhone = rsTmp.getString("customerPhone");
                        accountName = rsTmp.getString("customerName");
                    }
                } else if ("职工".equals(accountType)) {
                    sql = "SELECT * FROM employee WHERE employeeID = '" + accountID + "'";
                    Connection conTmp = DriverManager.getConnection(url, user, password);
                    Statement statTmp = conTmp.createStatement();
                    ResultSet rsTmp = statTmp.executeQuery(sql);
                    while (rsTmp.next()) {
                        accountPhone = rsTmp.getString("employeePhone");
                        accountName = rsTmp.getString("employeeName");
                    }
                } else if ("酒店管理员".equals(accountType)) {
                    sql = "SELECT * FROM hoteladmin WHERE hotelAdminID = '" + accountID + "'";
                    Connection conTmp = DriverManager.getConnection(url, user, password);
                    Statement statTmp = conTmp.createStatement();
                    ResultSet rsTmp = statTmp.executeQuery(sql);
                    while (rsTmp.next()) {
                        accountPhone = rsTmp.getString("hotelAdminPhone");
                        accountName = rsTmp.getString("hotelAdminName");
                    }
                } else if ("系统管理员".equals(accountType)) {
                    sql = "SELECT * FROM systemadmin WHERE systemAdminID = '" + accountID + "'";
                    Connection conTmp = DriverManager.getConnection(url, user, password);
                    Statement statTmp = conTmp.createStatement();
                    ResultSet rsTmp = statTmp.executeQuery(sql);
                    while (rsTmp.next()) {
                        accountPhone = rsTmp.getString("systemAdminPhone");
                        accountName = rsTmp.getString("systemAdminName");
                    }
                }

                queryByIDAccount = new Account(accountID,accountType,accountCode,accountName,accountPhone);

                return queryByIDAccount;

            }
        }

        return null;
    }


    public Vector<Vector> query() throws Exception{

        Vector<Vector> data = new Vector<>();

        Class.forName("com.mysql.cj.jdbc.Driver"); //注册驱动
        Connection con = DriverManager.getConnection(url, user, password);  //获取连接
        Statement stat = con.createStatement(); //获取执行者对象
        String sql = "SELECT * FROM account";
        ResultSet rs =stat.executeQuery(sql);

        while(rs.next()){

            String accountID = rs.getString("accountID");
            String accountCode = rs.getString("accountCode");
            String accountType = rs.getString("accountType");
            String accountPhone = null;
            String accountName = null;

            if("住客".equals(accountType)){
                sql = "SELECT * FROM customer WHERE customerID = '"+accountID+"'";
                Connection conTmp = DriverManager.getConnection(url, user, password);
                Statement statTmp = conTmp.createStatement();
                ResultSet rsTmp = statTmp.executeQuery(sql);
                while(rsTmp.next()){
                    accountPhone = rsTmp.getString("customerPhone");
                    accountName = rsTmp.getString("customerName");
                }
            } else if ("职工".equals(accountType)) {
                sql = "SELECT * FROM employee WHERE employeeID = '"+accountID+"'";
                Connection conTmp = DriverManager.getConnection(url, user, password);
                Statement statTmp = conTmp.createStatement();
                ResultSet rsTmp = statTmp.executeQuery(sql);
                while(rsTmp.next()){
                    accountPhone = rsTmp.getString("employeePhone");
                    accountName = rsTmp.getString("employeeName");
                }
            } else if ("酒店管理员".equals(accountType)) {
                sql = "SELECT * FROM hoteladmin WHERE hotelAdminID = '"+accountID+"'";
                Connection conTmp = DriverManager.getConnection(url, user, password);
                Statement statTmp = conTmp.createStatement();
                ResultSet rsTmp = statTmp.executeQuery(sql);
                while(rsTmp.next()){
                    accountPhone = rsTmp.getString("hotelAdminPhone");
                    accountName = rsTmp.getString("hotelAdminName");
                }
            } else if ("系统管理员".equals(accountType)) {
                sql = "SELECT * FROM systemadmin WHERE systemAdminID = '"+accountID+"'";
                Connection conTmp = DriverManager.getConnection(url, user, password);
                Statement statTmp = conTmp.createStatement();
                ResultSet rsTmp = statTmp.executeQuery(sql);
                while(rsTmp.next()){
                    accountPhone = rsTmp.getString("systemAdminPhone");
                    accountName = rsTmp.getString("systemAdminName");
                }
            }

            Vector line = new Vector();
            line.add(accountID);
            line.add(accountType);
            line.add(accountName);
            line.add(accountPhone);
            line.add(accountCode);

            data.add(line);

        }

        rs.close();
        stat.close();
        con.close();

        return data;
    }

    public Vector<Vector> queryByType(String getAccountType) throws Exception{

        Vector<Vector> data = new Vector<>();

        Class.forName("com.mysql.cj.jdbc.Driver"); //注册驱动
        Connection con = DriverManager.getConnection(url, user, password);  //获取连接
        Statement stat = con.createStatement(); //获取执行者对象
        String sql = "SELECT * FROM account WHERE accountType = '"+getAccountType+"'";
        ResultSet rs =stat.executeQuery(sql);

        while(rs.next()) {

            String accountID = rs.getString("accountID");
            String accountCode = rs.getString("accountCode");
            String accountType = rs.getString("accountType");
            String accountPhone = null;
            String accountName = null;

            if ("住客".equals(accountType)) {
                sql = "SELECT * FROM customer WHERE customerID = '" + accountID + "'";
                Connection conTmp = DriverManager.getConnection(url, user, password);
                Statement statTmp = conTmp.createStatement();
                ResultSet rsTmp = statTmp.executeQuery(sql);
                while (rsTmp.next()) {
                    accountPhone = rsTmp.getString("customerPhone");
                    accountName = rsTmp.getString("customerName");
                }
            } else if ("职工".equals(accountType)) {
                sql = "SELECT * FROM employee WHERE employeeID = '" + accountID + "'";
                Connection conTmp = DriverManager.getConnection(url, user, password);
                Statement statTmp = conTmp.createStatement();
                ResultSet rsTmp = statTmp.executeQuery(sql);
                while (rsTmp.next()) {
                    accountPhone = rsTmp.getString("employeePhone");
                    accountName = rsTmp.getString("employeeName");
                }
            } else if ("酒店管理员".equals(accountType)) {
                sql = "SELECT * FROM hoteladmin WHERE hotelAdminID = '" + accountID + "'";
                Connection conTmp = DriverManager.getConnection(url, user, password);
                Statement statTmp = conTmp.createStatement();
                ResultSet rsTmp = statTmp.executeQuery(sql);
                while (rsTmp.next()) {
                    accountPhone = rsTmp.getString("hotelAdminPhone");
                    accountName = rsTmp.getString("hotelAdminName");
                }
            } else if ("系统管理员".equals(accountType)) {
                sql = "SELECT * FROM systemadmin WHERE systemAdminID = '" + accountID + "'";
                Connection conTmp = DriverManager.getConnection(url, user, password);
                Statement statTmp = conTmp.createStatement();
                ResultSet rsTmp = statTmp.executeQuery(sql);
                while (rsTmp.next()) {
                    accountPhone = rsTmp.getString("systemAdminPhone");
                    accountName = rsTmp.getString("systemAdminName");
                }
            }

            Vector line = new Vector();
            line.add(accountID);
            line.add(accountType);
            line.add(accountName);
            line.add(accountPhone);
            line.add(accountCode);

            data.add(line);
        }
        return data;
    }
}
